package top.yinhe.study.javawebjdbc.dao;

import top.yinhe.study.javawebjdbc.entity.TbNews;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TbNewsDAO {

public static int insert(TbNews news)throws Exception{
    Connection conn = DBHelper.getConnection();
    String sql = "insert into tb_news(title,connect,published) values(?,?,?)";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setString(1, news.getTitle());
    ps.setString(2, news.getConnent());
    ps.setInt(3, news.getPublishdate());
    int ee = ps.executeUpdate();
    conn.close();
    return ee;
}

    public static List<TbNews> getAll() throws Exception {
        List<TbNews> list = new ArrayList<>();
        Connection conn = DBHelper.getConnection();
        String sql = "select * from tb_news";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            TbNews news = new TbNews();
            news.setId(rs.getInt("id"));
            news.setTitle(rs.getString("title"));
            news.setConnent(rs.getString("connent"));
            news.setPublishdate(rs.getInt("published"));
            list.add(news);
        }
        conn.close();
        return list;
    }

    public static int delete(Integer id) throws SQLException {
    Connection conn = DBHelper.getConnection();
    String sql = "delete from tb_news where id=?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setInt(1, id);
    int e = ps.executeUpdate();
    conn.close();
    return e;
    }
}
